using Microsoft.ApplicationBlocks.Data;
using Microsoft.AceEngineering.SPM.SPMCustomDataEntity;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;
using System.IO;
using System.Text;
using System.Web.UI.WebControls;
using System.Xml;

namespace Microsoft.AceEngineering.SPM.SPMDataLayer
{
    public class ApplicationsDL
    {
        public static void EnterApplication(ApplicationCDE app, string attributeList, string contactsListXML)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[10];
            myParams[0] = new SqlParameter("@ApplicationName", SqlDbType.VarChar, 50);
            myParams[0].Value = app.ApplicationName;

            myParams[1] = new SqlParameter("@version", SqlDbType.VarChar, 50);
            myParams[1].Value = app.Version;

            myParams[2] = new SqlParameter("@Description", SqlDbType.VarChar, 1000);
            myParams[2].Value = app.Description;

            myParams[3] = new SqlParameter("@StartDate", SqlDbType.DateTime);
            myParams[3].Value = app.StartDate;

            myParams[4] = new SqlParameter("@CompletionDate", SqlDbType.DateTime);
            myParams[4].Value = app.CompletionDate;

            myParams[5] = new SqlParameter("@OrganizationName", SqlDbType.VarChar, 50);
            myParams[5].Value = app.OrganizationName;

            myParams[6] = new SqlParameter("@Active", SqlDbType.Bit);
            myParams[6].Value = app.Active;

            myParams[7] = new SqlParameter("@CreatedBy", SqlDbType.VarChar, 50);
            myParams[7].Value = app.CreatedBy;

            myParams[8] = new SqlParameter("@attribute", SqlDbType.VarChar);
            myParams[8].Value = attributeList;

            myParams[9] = new SqlParameter("@contacts", SqlDbType.Xml);
            myParams[9].Value = contactsListXML;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spInsertApplicationData", myParams);

        }

        public static ApplicationCDE GetData(string name, int appID)
        {
            ArrayList attributes = null;
            ListItem lstItem = null;
            SqlDataReader sqlReader = null;
            ApplicationCDE appData = new ApplicationCDE();

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[2];
                myParams[0] = new SqlParameter("@ApplicationName", SqlDbType.VarChar, 50);
                myParams[0].Value = name;

                myParams[1] = new SqlParameter("@ApplicationID", SqlDbType.Int);
                myParams[1].Value = appID;

                sqlReader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spSelectApplicationData", myParams);

                if (sqlReader.FieldCount == 2) // Check for single row returned or more than one match.
                {
                    attributes = new ArrayList();
                    while (sqlReader.Read())
                    {
                        lstItem = new ListItem();
                        lstItem.Text = sqlReader.GetString(0) + "(" + sqlReader.GetValue(1).ToString() + ")";
                        lstItem.Value = sqlReader.GetValue(1).ToString();
                        attributes.Add(lstItem);
                        lstItem = null;
                    }
                    appData.ApplicationNameList = attributes;
                    return appData;
                }
                if (sqlReader.FieldCount > 2) // Check for single row returned or more than one match.
                {
                    if (sqlReader.Read())
                    {
                        appData.ApplicationName = sqlReader["ApplicationName"].ToString();
                        appData.ApplicationID = Convert.ToInt32(sqlReader["ApplicationID"]);
                        appData.Version = sqlReader["Version"].ToString();
                        appData.Description = sqlReader["Description"].ToString();
                        appData.StartDate = sqlReader["StartDate"].ToString();
                        appData.CompletionDate = sqlReader["CompletionDate"].ToString();

                        if (sqlReader["ActualCompletedDate"].ToString() != string.Empty)
                            appData.ActualCompletedDate = sqlReader["ActualCompletedDate"].ToString();
                        else
                            appData.ActualCompletedDate = "";

                        appData.OrgID = Int32.Parse(sqlReader["OrganizationID"].ToString());

                        if (sqlReader["Milestone"].ToString() == string.Empty)
                            appData.Milestone = string.Empty;
                        else
                            appData.Milestone = sqlReader["Milestone"].ToString();


                        if (sqlReader["Active"].ToString() != string.Empty)
                            appData.Active = (bool)sqlReader["Active"];
                    }
                }
            }
            finally
            {
                if (sqlReader != null)
                {
                    if (!sqlReader.IsClosed)
                        sqlReader.Close();
                }
            }
            return appData;
        }

        public static ArrayList GetSearchApplicationData(string name, int appID)
        {
            ArrayList apps = new ArrayList();
            ListItem lstItem = null;
            SqlDataReader sqlReader = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[2];
                myParams[0] = new SqlParameter("@ApplicationName", SqlDbType.VarChar, 50);
                myParams[0].Value = name;

                myParams[1] = new SqlParameter("@ApplicationID", SqlDbType.Int);
                myParams[1].Value = appID;

                sqlReader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spSelectSearchApplicationData", myParams);
                while (sqlReader.Read())
                {
                    lstItem = new ListItem();
                    lstItem.Text = sqlReader.GetString(0) + "(" + sqlReader.GetValue(1).ToString() + ")";
                    lstItem.Value = sqlReader.GetValue(1).ToString();
                    apps.Add(lstItem);
                    lstItem = null;
                }
            }
            finally
            {
                if (sqlReader != null)
                {
                    if (!sqlReader.IsClosed)
                        sqlReader.Close();
                }
            }
            return apps;
        }

        public static void UpdateApplication(ApplicationCDE app, string attributes, string contactsListXML)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[13];
            myParams[0] = new SqlParameter("@applicationID", SqlDbType.Int);
            myParams[0].Value = app.ApplicationID;

            myParams[1] = new SqlParameter("@ApplicationName", SqlDbType.VarChar, 50);
            myParams[1].Value = app.ApplicationName;

            myParams[2] = new SqlParameter("@version", SqlDbType.VarChar, 50);
            myParams[2].Value = app.Version;

            myParams[3] = new SqlParameter("@description", SqlDbType.VarChar, 1000);
            myParams[3].Value = app.Description;

            myParams[4] = new SqlParameter("@startDate", SqlDbType.DateTime);
            myParams[4].Value = app.StartDate;

            myParams[5] = new SqlParameter("@completionDate", SqlDbType.DateTime);
            myParams[5].Value = app.CompletionDate;

            //Check for NULL.
            myParams[6] = new SqlParameter("@actualCompletedDate", SqlDbType.DateTime);
            if (string.IsNullOrEmpty(app.ActualCompletedDate))
                myParams[6].Value = null;
            else
                myParams[6].Value = app.ActualCompletedDate;

            myParams[7] = new SqlParameter("@orgID", SqlDbType.Int);
            myParams[7].Value = app.OrgID;

            myParams[8] = new SqlParameter("@active", SqlDbType.Bit);
            myParams[8].Value = app.Active;

            myParams[9] = new SqlParameter("@modifiedBy", SqlDbType.VarChar, 50);
            myParams[9].Value = app.ModifiedBy;

            myParams[10] = new SqlParameter("@attribute", SqlDbType.VarChar);
            myParams[10].Value = attributes;

            myParams[11] = new SqlParameter("@contacts", SqlDbType.Xml);
            myParams[11].Value = contactsListXML;

            myParams[12] = new SqlParameter("@appNameID", SqlDbType.Int);
            myParams[12].Value = app.ApplicationID;

            Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spUpdateApplicationData", myParams);
        }

        public static ApplicationCDE GetApplicationDataByID(int appID)
        {
            SqlDataReader sqlReader = null;
            ApplicationCDE appData = new ApplicationCDE();

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@ApplicationID", SqlDbType.Int);
                myParams[0].Value = appID;

                sqlReader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spSelectApplicationDataByID", myParams);

                if (sqlReader.Read())
                {
                    appData.ApplicationID = Convert.ToInt32(sqlReader["ApplicationID"]);
                    appData.ApplicationName = sqlReader["ApplicationName"].ToString();
                    appData.OrgID = Int32.Parse(sqlReader["OrganizationID"].ToString());
                    appData.Version = sqlReader["Version"].ToString();
                    appData.Description = sqlReader["Description"].ToString();

                    appData.StartDate = sqlReader["StartDate"].ToString();
                    appData.CompletionDate = sqlReader["CompletionDate"].ToString();
                    appData.ActualCompletedDate = sqlReader["ActualCompletedDate"].ToString();
                    appData.ContactAlias = sqlReader["ContactAlias"].ToString();
                    appData.Milestone = sqlReader["Milestone"].ToString();
                    appData.OrganizationName = sqlReader["OrgName"].ToString();
                }
            }
            finally
            {
                if (sqlReader != null)
                {
                    if (!sqlReader.IsClosed)
                        sqlReader.Close();
                }
            }
            return appData;
        }

        public static List<ApplicationContactCDE> GetContactsByAppID(int appID)
        {
            List<ApplicationContactCDE> contactList = new List<ApplicationContactCDE>();
            SqlDataReader sqlReader = null;
            ApplicationContactCDE contact = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@AppID", SqlDbType.Int);
                myParams[0].Value = appID;

                sqlReader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spSelectApplicationContacts", myParams);

                while (sqlReader.Read())
                {
                    contact = new ApplicationContactCDE();
                    contact.Alias = sqlReader["ContactAlias"].ToString();
                    contact.ContactTypeID = sqlReader["ContactTypeID"].ToString();
                    contact.ContactTypeName = sqlReader["ContactTypeName"].ToString();
                    contact.Active = Boolean.Parse(sqlReader["active"].ToString());
                    contact.Email = sqlReader["ContactEmail"].ToString();
                    contact.Name = sqlReader["ContactName"].ToString();
                    contactList.Add(contact);
                    contact = null;
                }
            }
            finally
            {
                if (sqlReader != null)
                {
                    if (!sqlReader.IsClosed)
                        sqlReader.Close();
                }
            }
            return contactList;
        }

        public static string InsertApplication(ApplicationCDE app, string attributeList, string contactsListXML)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[11];
            myParams[0] = new SqlParameter("@ApplicationName", SqlDbType.VarChar, 50);
            myParams[0].Value = app.ApplicationName;
            myParams[0].Direction = ParameterDirection.Input;

            myParams[1] = new SqlParameter("@version", SqlDbType.VarChar, 50);
            myParams[1].Value = app.Version.ToString();
            myParams[1].Direction = ParameterDirection.Input;

            myParams[2] = new SqlParameter("@Description", SqlDbType.VarChar, 1000);
            myParams[2].Value = app.Description;
            myParams[2].Direction = ParameterDirection.Input;

            myParams[3] = new SqlParameter("@StartDate", SqlDbType.DateTime);
            myParams[3].Value = app.StartDate;
            myParams[3].Direction = ParameterDirection.Input;

            myParams[4] = new SqlParameter("@CompletionDate", SqlDbType.DateTime);
            myParams[4].Value = app.CompletionDate;
            myParams[4].Direction = ParameterDirection.Input;

            myParams[5] = new SqlParameter("@OrgID", SqlDbType.Int);
            myParams[5].Value = app.OrgID;
            myParams[5].Direction = ParameterDirection.Input;

            myParams[6] = new SqlParameter("@Active", SqlDbType.Bit);
            myParams[6].Value = app.Active;
            myParams[6].Direction = ParameterDirection.Input;

            myParams[7] = new SqlParameter("@CreatedBy", SqlDbType.VarChar, 50);
            myParams[7].Value = app.CreatedBy;
            myParams[7].Direction = ParameterDirection.Input;

            myParams[8] = new SqlParameter("@attribute", SqlDbType.VarChar);
            myParams[8].Value = attributeList;
            myParams[8].Direction = ParameterDirection.Input;

            myParams[9] = new SqlParameter("@contacts", SqlDbType.Xml);
            myParams[9].Value = contactsListXML;
            myParams[9].Direction = ParameterDirection.Input;

            myParams[10] = new SqlParameter("@appID", SqlDbType.Int);
            myParams[10].Direction = ParameterDirection.ReturnValue;

            Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "SPM_spInsertApplicationData", myParams);

            return myParams[10].Value.ToString();
        }

        public static List<PropertyCDE> GetSelectedAttributeData(int appID)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;
            SqlDataReader sqlreader = null;
            List<PropertyCDE> attList = new List<PropertyCDE>();
            PropertyCDE property = new PropertyCDE();

            try
            {
                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@appID", SqlDbType.Int, 50);
                myParams[0].Value = appID;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spSelectApplicationAttribute", myParams);

                while (sqlreader.Read())
                {
                    property = new PropertyCDE();
                    property.AttributeID = sqlreader["AttributeID"].ToString();
                    property.AttributeName = sqlreader["attributeName"].ToString();
                    property.Active = Boolean.Parse(sqlreader["active"].ToString());
                    attList.Add(property);
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }

            return attList;
        }
    }
}
